﻿using System;
using System.Collections.Generic;
using System.Linq;
using ud_Utilities;

namespace ud_Article.Business
{
    public class DBArticleLinq
    {
        readonly LinqArticle db = new LinqArticle();

        public ud_Article GetOne(int itemID)
        {
            var result = from p in db.ud_Articles
                         where p.ItemID.Equals(itemID)
                         select p;
            return result.FirstOrDefault();
        }

        public List<ud_ArticleCategory> GetCategories(int moduleID)
        {
            var result = from p in db.ud_ArticleCategories
                         where p.ModuleID.Equals(moduleID)
                         select p;
            return result.ToList();
        }

        public List<ud_Article> GetAll(int moduleID)
        {
            var result = from p in db.ud_Articles
                         where p.ModuleID.Equals(moduleID) && p.IsPublished == true
                         orderby p.StartDate descending
                         select p;
            return result.ToList();
        }

        public List<ud_Article> GetAllByCategoryID(int categoryID)
        {
            var listItem = from p in db.ud_ArticleItemCategories
                           where p.CategoryID == categoryID
                           select p.ItemID;
            var result = from p in db.ud_Articles
                         where listItem.ToList().Contains(p.ItemID)
                         orderby p.StartDate descending
                         select p;
            return result.ToList();
        }

        public List<ud_Article> SearchByTitle(int siteID, int moduleID, int categoryID, string searchTerm)
        {
            List<ud_Article> result = new List<ud_Article>();
            List<ud_Article> allArticles;
            searchTerm = searchTerm.ToLowerInvariant();
            if (moduleID != -1)
            {
                if (categoryID != -1)
                {
                    var listItem = from p in db.ud_ArticleItemCategories
                                   where p.CategoryID == categoryID
                                   select p.ItemID;
                    allArticles = (from p in db.ud_Articles
                                   where p.ModuleID == moduleID && listItem.ToList().Contains(p.ItemID)
                                   select p).ToList();
                }
                else
                {
                    allArticles = (from p in db.ud_Articles
                                   where p.ModuleID == moduleID
                                   select p).ToList();
                }
            }
            else
            {
                var listModuleID = from p in db.ud_Modules
                                   where p.SiteID == siteID
                                   select p.ModuleID;
                allArticles = (from p in db.ud_Articles
                               where listModuleID.ToList().Contains(p.ModuleID)
                               select p).ToList();
            }
            foreach (ud_Article article in allArticles)
            {
                if (article.Heading.ToLowerInvariant().Contains(searchTerm) || Utilities.ConvertToUnsign(article.Heading).ToLowerInvariant().Contains(searchTerm) || searchTerm == string.Empty)
                {
                    result.Add(article);
                }
            }
            return result;
        }

        public List<ud_Article> SearchByContent(int siteID, int moduleID, int categoryID, string searchTerm)
        {
            List<ud_Article> result = new List<ud_Article>();
            List<ud_Article> allArticles;
            searchTerm = searchTerm.ToLowerInvariant();
            if (moduleID != -1)
            {
                if (categoryID != -1)
                {
                    var listItem = from p in db.ud_ArticleItemCategories
                                   where p.CategoryID == categoryID
                                   select p.ItemID;
                    allArticles = (from p in db.ud_Articles
                                   where p.ModuleID == moduleID && listItem.ToList().Contains(p.ItemID)
                                   select p).ToList();
                }
                else
                {
                    allArticles = (from p in db.ud_Articles
                                   where p.ModuleID == moduleID
                                   select p).ToList();
                }
            }
            else
            {
                var listModuleID = from p in db.ud_Modules
                                   where p.SiteID == siteID
                                   select p.ModuleID;
                allArticles = (from p in db.ud_Articles
                               where listModuleID.ToList().Contains(p.ModuleID)
                               select p).ToList();
            }
            foreach (ud_Article article in allArticles)
            {
                if (article.Description.ToLowerInvariant().Contains(searchTerm) || article.Abstract.ToLowerInvariant().Contains(searchTerm) || Utilities.ConvertToUnsign(article.Description).ToLowerInvariant().Contains(searchTerm) || Utilities.ConvertToUnsign(article.Abstract).ToLowerInvariant().Contains(searchTerm) || searchTerm == string.Empty)
                {
                    result.Add(article);
                }
            }
            return result;
        }

        public List<ud_Article> Search(int siteID, int moduleID, int categoryID, string searchTerm)
        {
            List<ud_Article> result = new List<ud_Article>();
            List<ud_Article> allArticles;
            searchTerm = searchTerm.ToLowerInvariant();
            if (moduleID != -1)
            {
                if (categoryID != -1)
                {
                    var listItem = from p in db.ud_ArticleItemCategories
                                   where p.CategoryID == categoryID
                                   select p.ItemID;
                    allArticles = (from p in db.ud_Articles
                                   where p.ModuleID == moduleID && listItem.ToList().Contains(p.ItemID)
                                   select p).ToList();
                }
                else
                {
                    allArticles = (from p in db.ud_Articles
                                   where p.ModuleID == moduleID
                                   select p).ToList();
                }
            }
            else
            {
                var listModuleID = from p in db.ud_Modules
                                   where p.SiteID == siteID
                                   select p.ModuleID;
                allArticles = (from p in db.ud_Articles
                               where listModuleID.ToList().Contains(p.ModuleID)
                               select p).ToList();
            }
            foreach (ud_Article article in allArticles)
            {
                if (article.Heading.ToLowerInvariant().Contains(searchTerm) || article.Description.ToLowerInvariant().Contains(searchTerm) || article.Abstract.ToLowerInvariant().Contains(searchTerm) || Utilities.ConvertToUnsign(article.Heading).ToLowerInvariant().Contains(searchTerm) || Utilities.ConvertToUnsign(article.Description).ToLowerInvariant().Contains(searchTerm) || Utilities.ConvertToUnsign(article.Abstract).ToLowerInvariant().Contains(searchTerm) || searchTerm == string.Empty)
                {
                    result.Add(article);
                }
            }
            return result;
        }

        public List<ud_Article> GetPageByModuleId(int moduleId, int pageSize, int currentPage, out int totalPages)
        {
            var result = from p in db.ud_Articles
                         where p.ModuleID.Equals(moduleId) && p.IsPublished == true && p.StartDate < DateTime.UtcNow
                         orderby p.StartDate descending
                         select p;
            totalPages = pageSize == 0 ? 1 : result.Count() / pageSize;
            if (pageSize != 0 && (result.Count() % pageSize) != 0 && result.Count() > pageSize)
            {
                totalPages++;
            }
            return result.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        public List<ud_Article> GetOthersPageByModuleId(int moduleId, int pageSize, int currentPage)
        {
            var result = (from p in db.ud_Articles
                          where p.ModuleID.Equals(moduleId) && p.IsPublished == true && p.StartDate < DateTime.UtcNow
                          orderby p.StartDate descending
                          select p).ToList();
            if (result.Count > pageSize * currentPage)
            {
                result.RemoveRange((currentPage - 1) * pageSize , pageSize);
            }
            else
            {
                result.Clear();
            }
            return result;
        }

        public List<ud_Article> GetPageCategory(int categoryID, int pageSize, int currentPage, out int totalPages)
        {
            var listChild = from c in db.ud_ArticleCategories
                            where c.ParentID == categoryID
                            select c.CategoryID;
            var listItem = from q in db.ud_ArticleItemCategories
                           where q.CategoryID == categoryID || listChild.ToList().Contains(q.CategoryID)
                           select q.ItemID;
            var result = from p in db.ud_Articles
                         where listItem.ToList().Contains(p.ItemID) && p.IsPublished == true && p.StartDate < DateTime.UtcNow
                         orderby p.StartDate descending
                         select p;
            totalPages = pageSize == 0 ? 1 : result.Count() / pageSize;
            if (pageSize != 0 && (result.Count() % pageSize) != 0 && result.Count() > pageSize)
            {
                totalPages++;
            }
            return result.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        public List<ud_Article> GetPageCategoryReverse(int categoryID, int pageSize, int currentPage, out int totalPages)
        {
            var listChild = from c in db.ud_ArticleCategories
                            where c.ParentID == categoryID
                            select c.CategoryID;
            var listItem = from q in db.ud_ArticleItemCategories
                           where q.CategoryID == categoryID || listChild.ToList().Contains(q.CategoryID)
                           select q.ItemID;
            var result = from p in db.ud_Articles
                         where listItem.ToList().Contains(p.ItemID) && p.IsPublished == true && p.StartDate < DateTime.UtcNow
                         orderby p.StartDate ascending
                         select p;
            totalPages = pageSize == 0 ? 1 : result.Count() / pageSize;
            if (pageSize != 0 && (result.Count() % pageSize) != 0 && result.Count() > pageSize)
            {
                totalPages++;
            }
            return result.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        public List<ud_Article> GetOthersPageCategory(int categoryID, int pageSize, int currentPage)
        {
            var listChild = from c in db.ud_ArticleCategories
                            where c.ParentID == categoryID
                            select c.CategoryID;
            var listItem = from q in db.ud_ArticleItemCategories
                           where q.CategoryID == categoryID || listChild.ToList().Contains(q.CategoryID)
                           select q.ItemID;
            var result = (from p in db.ud_Articles
                          where listItem.ToList().Contains(p.ItemID) && p.IsPublished == true && p.StartDate < DateTime.UtcNow
                          orderby p.StartDate descending
                          select p).ToList();
            if (result.Count > pageSize * currentPage)
            {
                result.RemoveRange((currentPage - 1) * pageSize , pageSize);
            }
            else
            {
                result.Clear();
            }
            return result;
        }

        public List<ud_Article> GetArticlesByCategory(int categoryID)
        {
            List<ud_Article> result = new List<ud_Article>();
            var items = from p in db.ud_ArticleItemCategories
                        where p.ud_ArticleCategory.CategoryID.Equals(categoryID)
                        select p;
            foreach (ud_ArticleItemCategory item in items)
            {
                ud_Article temp = (from p in db.ud_Articles
                                   where p.ItemID.Equals(item.ItemID) && p.IsPublished == true && p.StartDate < DateTime.UtcNow
                                   orderby p.StartDate descending
                                   select p).FirstOrDefault();
                result.Add(temp);
            }
            return result;
        }

        public List<ud_Article> GetArticleByModuleID(int moduleID)
        {
            var result = from p in db.ud_Articles
                         where p.ModuleID.Equals(moduleID) && p.IsPublished == true && p.StartDate < DateTime.UtcNow
                         orderby p.StartDate descending
                         select p;
            return result.ToList();
        }

        public List<ud_Article> GetArticleByCategory(int moduleID, int categoryID)
        {
            var listItem = from q in db.ud_ArticleItemCategories
                           where q.CategoryID == categoryID
                           select q.ItemID;
            var result = from p in db.ud_Articles
                         where p.ModuleID.Equals(moduleID) && listItem.ToList().Contains(p.ItemID) && p.IsPublished == true && p.StartDate < DateTime.UtcNow
                         orderby p.StartDate descending
                         select p;
            return result.ToList();
        }

        public List<ud_Article> GetOtherArticlesByItemID(int itemID, int moduleID)
        {
            var result = from p in db.ud_Articles
                         where !p.ItemID.Equals(itemID) && p.ModuleID.Equals(moduleID) && p.IsPublished == true && p.StartDate < DateTime.UtcNow
                         orderby p.StartDate descending
                         select p;
            return result.ToList();
        }

        public List<ud_Article> GetOtherArticlesByItemID(int itemID, int moduleID, int categoryID)
        {
            var listChild = from c in db.ud_ArticleCategories
                            where c.ParentID == categoryID
                            select c.CategoryID;
            var listItem = from q in db.ud_ArticleItemCategories
                           where q.CategoryID == categoryID || listChild.ToList().Contains(q.CategoryID)
                           select q.ItemID;
            var result = from p in db.ud_Articles
                         where !p.ItemID.Equals(itemID) && listItem.ToList().Contains(p.ItemID) && p.ModuleID.Equals(moduleID) && p.IsPublished == true && p.StartDate < DateTime.UtcNow
                         orderby p.StartDate descending
                         select p;
            return result.ToList();
        }

        public List<ud_Article> GetPageMostViewModule(string[] listModuleID, int pageSize, int currentPage, out int totalPages)
        {
            var result = from p in db.ud_Articles
                         where listModuleID.Contains(p.ModuleID.ToString()) && p.IsPublished == true
                         orderby p.HitCount descending
                         select p;
            totalPages = pageSize == 0 ? 1 : result.Count() / pageSize;
            if (pageSize != 0 && (result.Count() % pageSize) != 0 && result.Count() > pageSize)
            {
                totalPages++;
            }
            return result.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        public List<ud_Article> GetPageModule(string[] listModuleID, int pageSize, int currentPage, out int totalPages)
        {
            var result = from p in db.ud_Articles
                         where listModuleID.Contains(p.ModuleID.ToString()) && p.IsPublished == true
                         orderby p.StartDate descending
                         select p;
            totalPages = pageSize == 0 ? 1 : result.Count() / pageSize;
            if (pageSize != 0 && (result.Count() % pageSize) != 0 && result.Count() > pageSize)
            {
                totalPages++;
            }
            return result.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        public List<ud_Article> GetOthersPageModule(string[] listModuleID, int pageSize, int currentPage)
        {
            var result = (from p in db.ud_Articles
                          where listModuleID.Contains(p.ModuleID.ToString()) && p.IsPublished == true
                          orderby p.StartDate descending
                          select p).ToList();
            if (result.Count > pageSize * currentPage)
            {
                result.RemoveRange((currentPage - 1) * pageSize, pageSize);
            }
            else
            {
                result.Clear();
            }
            return result;
        }

        public List<ud_Article> GetOthersPageMostViewModule(string[] listModuleID, int pageSize, int currentPage)
        {
            var result = (from p in db.ud_Articles
                         where listModuleID.Contains(p.ModuleID.ToString()) && p.IsPublished == true
                         orderby p.HitCount descending
                         select p).ToList();
            if (result.Count > pageSize * currentPage)
            {
                result.RemoveRange((currentPage - 1) * pageSize , pageSize);
            }
            else
            {
                result.Clear();
            }
            return result;
        }

        public List<ud_Article> GetPageHotModule(string[] listModuleID, int pageSize, int currentPage, out int totalPages)
        {
            var result = from p in db.ud_Articles
                         where listModuleID.Contains(p.ModuleID.ToString()) && p.IsPublished == true && p.IsHot == true
                         orderby p.StartDate descending
                         select p;
            totalPages = pageSize == 0 ? 1 : result.Count() / pageSize;
            if (pageSize != 0 && (result.Count() % pageSize) != 0 && result.Count() > pageSize)
            {
                totalPages++;
            }
            return result.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        public List<ud_Article> GetOthersPageHotModule(string[] listModuleID, int pageSize, int currentPage)
        {
            var result = (from p in db.ud_Articles
                         where listModuleID.Contains(p.ModuleID.ToString()) && p.IsPublished == true && p.IsHot == true
                         orderby p.StartDate descending
                         select p).ToList();
            if (result.Count > pageSize * currentPage)
            {
                result.RemoveRange((currentPage - 1) * pageSize , pageSize);
            }
            else
            {
                result.Clear();
            }
            return result;
        }
    }
}
